﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Ay.Framework.WPF;
using Base.DAL;
using MPSystem.Execl;
using MPSystem.Util;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

namespace Base.BLL
{
    /// <summary>
    /// 综采数据服务
    /// </summary>
    class DataZcService
    {
        private static readonly int _perPage = 10000;
        #region 综采在线监测
        /// <summary>
        /// 获取实时数据
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="gzmbh">工作面编号</param>
        /// <returns>所有分站实时数据</returns>
        public CurveZc GetActive(int cqbh, int gzmbh)
        {
            CurveZc rt = new CurveZc();
            DateTime dt = DateTime.Now;
            foreach (var _d in DataParseService.GetCache(DATA_TYPE.ZCSJ))
            {
                View_ZC_Active d = _d as View_ZC_Active;
                if (d == null) return null;
                if (d.CQBH != cqbh || d.GZMBH != gzmbh) continue;
                
                rt.X.Add(d.DYZJBH + "#" + d.ZBH);
                rt.T.Add(d.ZDATETIME);
                rt.WZ.Add(d.WZ);
                //超过规定时间无数据则视为故障，单位为秒
                int timeout = int.Parse(ConfigService.GetIni("Timeout", "600"));
                if ((dt - d.ZDATETIME).TotalSeconds >= timeout)
                {
                    rt.S.Add("离线");
                    rt.P1.Add(0);
                    rt.P2.Add(0);
                    rt.P3.Add(0);
                    rt.WarnNow = true;
                }
                else
                {
                    rt.S.Add((d.P1==0 && d.P2==0 && d.P3==0)? "故障" : "正常");
                    rt.P1.Add(d.P1);
                    rt.P2.Add(d.P2);
                    rt.P3.Add(d.P3);
                    if (d.SFBJ) rt.WarnNow = true;
                }
            }
            return rt;
            //示例：GetActive(1, 1);
        }
        /// <summary>
        /// 获取报警列表信息
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="gzmbh">工作面编号</param>
        /// <returns>包含报警信息的结果对象</returns>
        public CurveZc GetWarn(int cqbh, int gzmbh)
        {
            CurveZc rt = new CurveZc();
            DateTime dt = DateTime.Now;
            foreach (var _d in DataParseService.GetCache(DATA_TYPE.ZCSJ))
            {
                View_ZC_Active d = _d as View_ZC_Active;
                if (d == null) return null;
                if (d.CQBH != cqbh || d.GZMBH != gzmbh) continue;
                //超过规定时间无数据则视为故障
                int timeout = int.Parse(ConfigService.GetIni("Timeout", "600"));
                if ((dt - d.ZDATETIME).TotalSeconds >= timeout)
                {
                    rt.S.Add("通讯故障，请检查设备状况");
                    rt.X.Add(d.ZBH.ToString());
                    rt.T.Add(d.ZDATETIME);
                    rt.WZ.Add(d.WZ);
                }
                else
                if (d.SFBJ)
                {
                    rt.S.Add("警告");
                    rt.X.Add(d.ZBH.ToString());
                    rt.T.Add(d.ZDATETIME);
                    rt.WZ.Add(d.WZ);
                }
            }
            return rt;
        }
        #endregion

        #region 综采数据查询
        /// <summary>
        /// 查询综采数据
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="gzmbh">工作面编号</param>
        /// <param name="byZBH">true:按压力分机查询 false:综采支架查询</param>
        /// <param name="bhs">编号列表</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <param name="jq">加权，0代表原始数据</param>
        /// <param name="where">条件语句，如"P1>=10 AND P2=20"</param>
        /// <param name="sfbj">是否报警，-1：全部 0：未报警 1：报警</param>
        /// <param name="page">分页页数，从0开始</param>
        /// <param name="perPage">每页数量，默认20</param>
        /// <returns>综采数据列表</returns>
        public IEnumerable<View_ZC_Active> Query(int cqbh, int gzmbh, bool byZBH, int[] bhs, DateTime dtStart, DateTime dtEnd, int jq, string where, int sfbj, int page, int perPage = 120)
        {
            int[] jqs = new int[] { 0, 2, 4, 8, 12, 24 };
            if (!jqs.Contains(jq))
            {
                return null;
            }
            string sql = @"SELECT ZDATETIME,ZBH,DYZJBH,WZ,P1,P2,P3,SFBJ
                            FROM (SELECT ROW_NUMBER() OVER(ORDER by ZDATETIME,ZBH) ROWID,@tb.ZDATETIME,@tb.ZBH,ZCYLFJWZB.DYZJBH,ZCYLFJWZB.WZ,@tb.P1,@tb.P2,@tb.P3,@tb.SFBJ
	                            FROM @tb INNER JOIN ZCYLFJWZB ON ZCYLFJWZB.YLFJBH=@tb.ZBH
	                            WHERE SYZT=1 AND @tb.CQBH=@cqbh AND ZCYLFJWZB.GZMBH=@gzmbh AND @by IN @bhs
                                AND ZDATETIME>=@dtStart AND ZDATETIME<=@dtEnd AND (@where)
                            ) Tb WHERE Tb.ROWID BETWEEN @page*@perPage+1 AND (@page+1)*@perPage";

            sql = sql.Replace("@tb", jq == 0 ? "ZCSJB" : "ZCSJB_JQ" + jq);
            sql = sql.Replace("@by", byZBH ? "ZBH" : "DYZJBH");
            string extra = where == null || where == string.Empty ? "1=1" : where;
            if (sfbj >= 0)
            {
                extra += " AND SFBJ=@sfbj";
            }
            sql = sql.Replace("@where", extra);
            return Db.Query<View_ZC_Active>(sql, new { cqbh = cqbh, gzmbh = gzmbh, bhs = bhs, dtStart = dtStart, dtEnd = dtEnd, sfbj = sfbj, page = page, perPage = perPage });
        }
        /// <summary>
        /// 查询综采数据总数
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="gzmbh">工作面编号</param>
        /// <param name="byZBH">true:按压力分机查询 false:综采支架查询</param>
        /// <param name="bhs">编号列表</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <param name="jq">加权，0代表原始数据</param>
        /// <param name="where">条件语句，如"P1>=10 AND P2=20"</param>
        /// <param name="sfbj">是否报警，-1：全部 0：未报警 1：报警</param>
        /// <returns>综采数据列表</returns>
        public int QueryCount(int cqbh, int gzmbh, bool byZBH, int[] bhs, DateTime dtStart, DateTime dtEnd, int jq, string where, int sfbj)
        {
            int[] jqs = new int[] { 0, 2, 4, 8, 12, 24 };
            if (!jqs.Contains(jq))
            {
                return 0;
            }
            string sql = @"SELECT COUNT(1) FROM @tb INNER JOIN ZCYLFJWZB ON ZCYLFJWZB.YLFJBH=@tb.ZBH
	                            WHERE SYZT=1 AND @tb.CQBH=@cqbh AND ZCYLFJWZB.GZMBH=@gzmbh AND @by IN @bhs
                                AND ZDATETIME>=@dtStart AND ZDATETIME<=@dtEnd AND (@where)";

            sql = sql.Replace("@tb", jq == 0 ? "ZCSJB" : "ZCSJB_JQ" + jq);
            sql = sql.Replace("@by", byZBH ? "ZBH" : "DYZJBH");
            string extra = where == null || where == string.Empty ? "1=1" : where;
            if (sfbj >= 0)
            {
                extra += " AND SFBJ=@sfbj";
            }
            sql = sql.Replace("@where", extra);
            return Db.Count(sql, new { cqbh = cqbh, gzmbh = gzmbh, bhs = bhs, dtStart = dtStart, dtEnd = dtEnd, sfbj = sfbj });
        }
        #endregion

        #region 综采数据导出
        /// <summary>
        /// 导出综采数据
        /// </summary>
        /// <param name="saveFileName">导出文件保存位置</param>
        /// <param name="cqbh">测区编号</param>
        /// <param name="gzmbh">工作面编号</param>
        /// <param name="byZBH">true:按压力分机查询 false:综采支架查询</param>
        /// <param name="bhs">编号列表</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <param name="jq">加权，0代表原始数据</param>
        /// <param name="where">条件语句，如"P1>=10 AND P2=20"</param>
        /// <param name="sfbj">是否报警，-1：全部 0：未报警 1：报警</param>
        public void Export(string saveFileName, int cqbh, int gzmbh, bool byZBH, int[] bhs, DateTime dtStart, DateTime dtEnd, int jq, string where, int sfbj)
        {
            //复制模板
            string dir = System.AppDomain.CurrentDomain.BaseDirectory;// System.Environment.CurrentDirectory;
            string tpl = dir + @"\Tpl\Query_ZC.xls";
            //File.Copy(tpl, saveFileName);
            int page = 0;

            //设置字体，大小，对齐方式
            using (var ftpl = File.OpenRead(tpl))
            {
                //创建主要对象
                var workbook = new HSSFWorkbook(ftpl);
                var sheet = workbook.GetSheetAt(0);
                sheet.GetRow(1).GetCell(6).SetCellValue(DateTime.Now.ToString("报表日期 yyyy-MM-dd HH:mm:ss"));

                HSSFCellStyle style = ExcelHelper.DefaultStyle(workbook);
                HSSFCellStyle _style = (HSSFCellStyle)workbook.CreateCellStyle();
                _style.CloneStyleFrom(style);
                _style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");

                int row = 3;
                while (true)
                {
                    var data = Query(cqbh, gzmbh, byZBH, bhs, dtStart, dtEnd, jq, where, sfbj, page++, _perPage);
                    if (data == null || data.Count() == 0)
                        break;
                    int i = 0;
                    for (; i < data.Count(); i++)
                    {                        
                        var dataRow = sheet.CreateRow(row + i);
                        int j = 0;
                        var d = data.ElementAt(i);
                        dataRow.CreateCell(j++).SetCellValue(d.ZDATETIME.ToString("yyyy-MM-dd HH:mm:ss"));
                        dataRow.CreateCell(j++).SetCellValue(d.ZBH);
                        dataRow.CreateCell(j++).SetCellValue(d.DYZJBH);
                        dataRow.CreateCell(j++).SetCellValue(d.WZ);
                        dataRow.CreateCell(j++).SetCellValue(d.P1 == 0 ? "--" : d.P1.ToString());
                        dataRow.CreateCell(j++).SetCellValue(d.P2 == 0 ? "--" : d.P2.ToString());
                        dataRow.CreateCell(j++).SetCellValue(d.P3 == 0 ? "--" : d.P3.ToString());
                        dataRow.CreateCell(j++).SetCellValue(d.Q1 == 0 ? "--" : d.Q1.ToString("f2"));
                        dataRow.CreateCell(j++).SetCellValue(d.Q2 == 0 ? "--" : d.Q2.ToString("f2"));
                        dataRow.CreateCell(j++).SetCellValue(d.P1 * d.P2 == 0 ? "故障" : (d.SFBJ ? "报警" : "正常"));
                        for (j--; j >= 0; j--)
                        {
                            if (j >= 4)
                            {
                                dataRow.GetCell(j).CellStyle = _style;
                            }
                            else
                            {
                                dataRow.GetCell(j).CellStyle = style;
                            }
                        }
                    }
                    row = row + i;
                }
                using (var fout = File.OpenWrite(saveFileName))
                {
                    workbook.Write(fout);
                }
            }
        }

        /// <summary>
        /// 导出综采分析数据
        /// </summary>
        /// <param name="saveFileName">导出文件保存位置</param>
        /// <param name="dw">单位，MPa或KN</param>
        /// <param name="cqbh">测区编号</param>
        /// <param name="gzmbh">工作面编号</param>
        /// <param name="bhs">压力分机编号列表</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <param name="jq">加权</param>
        public void ExportAnalysis(string saveFileName, string dw, int cqbh, int gzmbh, int[] bhs, DateTime dtStart, DateTime dtEnd, int jq)
        {

            //复制模板
            string dir = System.Environment.CurrentDirectory;
            string tpl = dir + @"\Tpl\Analysis_ZC.xls";
            //File.Copy(tpl, saveFileName);
            int page = 0;
            //设置字体，大小，对齐方式
            using (var ftpl = File.OpenRead(tpl))
            {
                //创建主要对象
                var workbook = new HSSFWorkbook(ftpl);
                var sheet = workbook.GetSheetAt(0);
                sheet.GetRow(1).GetCell(7).SetCellValue(DateTime.Now.ToString("报表日期 yyyy-MM-dd HH:mm:ss"));
                sheet.GetRow(2).GetCell(4).SetCellValue(string.Format("工作阻力最大值({0})", dw));
                sheet.GetRow(2).GetCell(8).SetCellValue(string.Format("工作阻力平均值({0})", dw));

                HSSFCellStyle style = ExportExcel.DefaultStyle(workbook);
                HSSFCellStyle _style = (HSSFCellStyle)workbook.CreateCellStyle();
                _style.CloneStyleFrom(style);
                _style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");

                int row = 4;
                while (true)
                {
                    var data = Analysis(cqbh, gzmbh, bhs, dtStart, dtEnd, jq, page++, _perPage);
                    if (data == null || data.Count() == 0)
                        break;
                    int i = 0;
                    int lastZBH = 0, lastRow = row;
                    for (; i < data.Count(); i++)
                    {
                        var dataRow = sheet.CreateRow(row + i);
                        int j = 0;
                        var d = data.ElementAt(i);
                        dataRow.CreateCell(j++).SetCellValue(d.ZBH);
                        dataRow.CreateCell(j++).SetCellValue(d.DYZJBH);
                        dataRow.CreateCell(j++).SetCellValue(d.WZ);
                        dataRow.CreateCell(j++).SetCellValue(d.ZDATETIME.ToString("yyyy-MM-dd HH:mm:ss"));
                        dataRow.CreateCell(j++).SetCellValue(dw == "MPa" ? d.MaxP12 : d.MaxF12);
                        dataRow.CreateCell(j++);//平均值
                        dataRow.CreateCell(j++);//均方差
                        dataRow.CreateCell(j++);//平均值+均方差
                        dataRow.CreateCell(j++).SetCellValue(dw == "MPa" ? d.AvgP12 : d.AvgF12);
                        dataRow.CreateCell(j++);//平均值
                        dataRow.CreateCell(j++);//均方差
                        dataRow.CreateCell(j++);//平均值+均方差
                        for (j--; j >= 0; j--)
                        {
                            if (j >= 3)
                            {
                                dataRow.GetCell(j).CellStyle = _style;
                            }
                            else
                            {
                                dataRow.GetCell(j).CellStyle = style;
                            }
                        }
                        //合并单元格并设置公式
                        int ZBH = d.ZBH;
                        if (lastZBH == 0 || lastZBH == ZBH)
                        {
                            //平均值
                            sheet.AddMergedRegion(new CellRangeAddress(lastRow, row + i, 5, 5));
                            sheet.GetRow(lastRow).GetCell(5).CellFormula = string.Format("AVERAGE(E{0}:E{1})", lastRow + 1, row + i + 1);
                            //均方差
                            sheet.AddMergedRegion(new CellRangeAddress(lastRow, row + i, 6, 6));
                            sheet.GetRow(lastRow).GetCell(6).CellFormula = string.Format("STDEVP(E{0}:E{1})", lastRow + 1, row + i + 1);
                            //平均值+均方差
                            sheet.AddMergedRegion(new CellRangeAddress(lastRow, row + i, 7, 7));
                            sheet.GetRow(lastRow).GetCell(7).CellFormula = string.Format("F{0}+G{1}", lastRow + 1, lastRow + 1);

                            //平均值
                            sheet.AddMergedRegion(new CellRangeAddress(lastRow, row + i, 9, 9));
                            sheet.GetRow(lastRow).GetCell(9).CellFormula = string.Format("AVERAGE(H{0}:H{1})", lastRow + 1, row + i + 1);
                            //均方差
                            sheet.AddMergedRegion(new CellRangeAddress(lastRow, row + i, 10, 10));
                            sheet.GetRow(lastRow).GetCell(10).CellFormula = string.Format("STDEVP(H{0}:H{1})", lastRow + 1, row + i + 1);
                            //平均值+均方差
                            sheet.AddMergedRegion(new CellRangeAddress(lastRow, row + i, 11, 11));
                            sheet.GetRow(lastRow).GetCell(11).CellFormula = string.Format("I{0}+J{1}", lastRow + 1, lastRow + 1);
                        }
                        if (lastZBH == 0 || lastZBH != ZBH)
                        {
                            lastZBH = ZBH;
                            lastRow = row + i;
                        }

                    }
                    row = row + i;
                }
                using (var fout = File.OpenWrite(saveFileName))
                {
                    workbook.Write(fout);
                }
            }
        }

        /// <summary>
        /// 综采报表参数
        /// </summary>
        public class ExportParam
        {
            public string ComanpyName = "";//公司名称
            public string DW = "MPa";//单位
            public string GZM = "";//工作面名称
            public string RZBH = "";//认证编号
            public bool WithChart = false;//是否生成曲线
            public double HGZ = 24;//合格值
            public double UpperLimit = 38;//上限值
            public double WarnLimit = 20;//报警值
            public string Sign = "";//签名
        }
        /// <summary>
        /// 导出综采报表
        /// </summary>
        /// <param name="saveFileName">导出文件保存位置</param>
        /// <param name="param">报表参数</param>
        /// <param name="cqbh">测区编号</param>
        /// <param name="gzmbh">工作面编号</param>
        /// <param name="zbhs">压力分站编号列表</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <param name="channel">立柱，0：整架 1左柱 2右柱</param>
        /// <param name="minP">初撑合格值</param>
        /// <param name="isMPa">单位是否为MPa</param>
        /// <returns></returns>
        public void ExportReport(string saveFileName, ExportParam param, int cqbh, int gzmbh, int[] zbhs, DateTime dtStart, DateTime dtEnd, int channel = 0)
        {
            //复制模板
            string dir = System.AppDomain.CurrentDomain.BaseDirectory;//System.Environment.CurrentDirectory;
            string tpl = dir + (param.WithChart ? @"\Tpl\Report_ZC_Chart.xls" : @"\Tpl\Report_ZC.xls");
            //File.Copy(tpl, saveFileName);
            //图表下的第一行
            int rowAfterChart = param.WithChart ? 11 : 3;
            //设置字体，大小，对齐方式
            using (var ftpl = File.OpenRead(tpl))
            {
                //创建主要对象
                var workbook = new HSSFWorkbook(ftpl);
                var sheet = workbook.GetSheetAt(0);
                //创建基本样式
                HSSFCellStyle style = ExportExcel.DefaultStyle(workbook);
                HSSFCellStyle _style = (HSSFCellStyle)workbook.CreateCellStyle();
                _style.CloneStyleFrom(style);
                IDataFormat dataformat = workbook.CreateDataFormat();
                _style.DataFormat = dataformat.GetFormat("0.0");
                //修改基本参数
                sheet.GetRow(0).GetCell(0).SetCellValue(param.ComanpyName);
                sheet.GetRow(1).GetCell(8).SetCellValue(DateTime.Now.ToString("报表日期 yyyy-MM-dd HH:mm:ss"));
                sheet.GetRow(2).GetCell(0).SetCellValue(
                    string.Format("工作面名称:{0}   认证编号:{1}   监测日期:{2}至{3}",
                    param.GZM, param.RZBH, dtStart.ToString("yyyy-MM-dd HH:mm:ss"), dtEnd.ToString("yyyy-MM-dd HH:mm:ss"))
                );
                sheet.GetRow(rowAfterChart).GetCell(0).SetCellValue(string.Format("监测数据统计表(单位{0})：", param.DW));
                //签名
                sheet.GetRow(rowAfterChart + 22).GetCell(0).SetCellValue(param.Sign);

                //计算统计
                var rt = Report(cqbh, gzmbh, zbhs, dtStart, dtEnd, channel, param.HGZ, param.DW == "MPa");
                //填写工作面工作阻力统计
                sheet.GetRow(rowAfterChart + 4).GetCell(2).SetCellValue(rt.WHOLE[0]);//整面
                sheet.GetRow(rowAfterChart + 4).GetCell(3).SetCellValue(rt.WHOLE[1]);
                sheet.GetRow(rowAfterChart + 5).GetCell(2).SetCellValue(rt.WHOLE[2]);
                sheet.GetRow(rowAfterChart + 4).GetCell(4).SetCellValue(rt.TOP[0]);//上部
                sheet.GetRow(rowAfterChart + 4).GetCell(6).SetCellValue(rt.TOP[1]);
                sheet.GetRow(rowAfterChart + 5).GetCell(4).SetCellValue(rt.TOP[2]);
                sheet.GetRow(rowAfterChart + 4).GetCell(8).SetCellValue(rt.MID[0]);//中部
                sheet.GetRow(rowAfterChart + 4).GetCell(10).SetCellValue(rt.MID[1]);
                sheet.GetRow(rowAfterChart + 5).GetCell(8).SetCellValue(rt.MID[2]);
                sheet.GetRow(rowAfterChart + 4).GetCell(12).SetCellValue(rt.BOTTOM[0]);//下部
                sheet.GetRow(rowAfterChart + 4).GetCell(14).SetCellValue(rt.BOTTOM[1]);
                sheet.GetRow(rowAfterChart + 5).GetCell(12).SetCellValue(rt.BOTTOM[2]);
                //填写工作阻力、初撑力、末阻力
                int rowList = rowAfterChart + 8;
                IRow mySourceRow = sheet.GetRow(rowList);
                ExcelHelper.InsertRow(sheet, rowList, rt.YLFZBH.Count() - 1, mySourceRow);
                for (int i = 0; i < rt.YLFZBH.Count(); i++)
                {
                    int j = 0;
                    var dataRow = sheet.GetRow(rowList + i);
                    dataRow.CreateCell(j++).SetCellValue(rt.YLFZBH[i]);
                    dataRow.CreateCell(j++).SetCellValue(rt.DYZJBH[i]);
                    dataRow.CreateCell(j++).SetCellValue(rt.WZ[i]);
                    dataRow.CreateCell(j++);
                    sheet.AddMergedRegion(new CellRangeAddress(rowList + i, rowList + i, j - 2, j - 1));//合并
                    dataRow.CreateCell(j++).SetCellValue(rt.MaxZL[i]);
                    dataRow.CreateCell(j++).SetCellValue(rt.AvgZL[i]);
                    dataRow.CreateCell(j++);
                    sheet.AddMergedRegion(new CellRangeAddress(rowList + i, rowList + i, j - 2, j - 1));//合并
                    dataRow.CreateCell(j++).SetCellValue(rt.MinCCL[i] == -1 ? "--" : rt.MinCCL[i].ToString("f1"));
                    dataRow.CreateCell(j++);
                    sheet.AddMergedRegion(new CellRangeAddress(rowList + i, rowList + i, j - 2, j - 1));//合并
                    dataRow.CreateCell(j++).SetCellValue(rt.AvgCCL[i] == -1 ? "--" : rt.AvgCCL[i].ToString("f1"));
                    dataRow.CreateCell(j++).SetCellValue(rt.MaxMZL[i] == -1 ? "--" : rt.MaxMZL[i].ToString("f1"));
                    dataRow.CreateCell(j++).SetCellValue(rt.AvgMZL[i] == -1 ? "--" : rt.AvgMZL[i].ToString("f1"));
                    dataRow.CreateCell(j++);
                    sheet.AddMergedRegion(new CellRangeAddress(rowList + i, rowList + i, j - 2, j - 1));//合并
                    dataRow.CreateCell(j++).SetCellValue("--");
                    dataRow.CreateCell(j++);
                    sheet.AddMergedRegion(new CellRangeAddress(rowList + i, rowList + i, j - 2, j - 1));//合并
                    dataRow.CreateCell(j++).SetCellValue("--");
                    if (param.WithChart)
                    {
                        dataRow.CreateCell(j++).SetCellValue(param.HGZ);
                        dataRow.CreateCell(j++).SetCellValue(param.UpperLimit);
                        dataRow.CreateCell(j++).SetCellValue(param.WarnLimit);
                    }
                    for (j--; j >= 0; j--)
                    {
                        if (j >= 4)
                        {
                            dataRow.GetCell(j).CellStyle = _style;
                        }
                        else
                        {
                            dataRow.GetCell(j).CellStyle = style;
                        }
                    }
                }
                //初撑力合格率
                sheet.GetRow(rowList + rt.YLFZBH.Count() + 1).GetCell(0).SetCellValue(string.Format("初撑力统计合格率:{0:f2}%", rt.PoP * 100));

                //保存
                using (var fout = File.OpenWrite(saveFileName))
                {
                    workbook.Write(fout);
                }
            }
        }
        #endregion

        #region 综采数据分析
        /// <summary>
        /// 分析综采数据
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="gzmbh">工作面编号</param>
        /// <param name="bhs">压力分机编号列表</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <param name="jq">加权</param>
        /// <param name="page">分页页数，从0开始</param>
        /// <param name="perPage">每页数量，默认20</param>
        /// <returns>分析报表</returns>
        public IEnumerable<AnalysisZc> Analysis(int cqbh, int gzmbh, int[] bhs, DateTime dtStart, DateTime dtEnd, int jq, int page, int perPage = 120)
        {
            int[] jqs = new int[] { 2, 4, 8, 12, 24 };
            if (!jqs.Contains(jq))
            {
                return null;
            }
            string sql = @"SELECT ZBH,DYZJBH,WZ,ZDATETIME,ZJJQ_M AvgP12,ZJJQ_K AvgF12,MaxP MaxP12,MaxF MaxF12
                            FROM (SELECT ROW_NUMBER() OVER(ORDER by ZBH,ZDATETIME) ROWID,ZBH,DYZJBH,WZ,ZDATETIME,ZJJQ_M,ZJJQ_K,MaxP,MaxF
	                            FROM @tb INNER JOIN ZCYLFJWZB ON ZCYLFJWZB.GZMBH=@tb.GZMBH AND ZCYLFJWZB.YLFJBH=@tb.ZBH
	                            WHERE SYZT=1 AND @tb.CQBH=@cqbh AND ZCYLFJWZB.GZMBH=@gzmbh AND ZBH IN @bhs
                                AND ZDATETIME>=@dtStart AND ZDATETIME<=@dtEnd
                            ) Tb WHERE Tb.ROWID BETWEEN @page*@perPage+1 AND (@page+1)*@perPage";

            sql = sql.Replace("@tb", "ZCSJB_JQ" + jq);
            return Db.Query<AnalysisZc>(sql, new { cqbh = cqbh, gzmbh = gzmbh, bhs = bhs, dtStart = dtStart, dtEnd = dtEnd, page = page, perPage = perPage });
        }
        /// <summary>
        /// 分析综采数据总数
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="gzmbh">工作面编号</param>
        /// <param name="bhs">压力分机编号列表</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <param name="jq">加权</param>
        /// <param name="page">分页页数，从0开始</param>
        /// <param name="perPage">每页数量，默认20</param>
        /// <returns>总数</returns>
        public int AnalysisCount(int cqbh, int gzmbh, int[] bhs, DateTime dtStart, DateTime dtEnd, int jq)
        {
            int[] jqs = new int[] { 2, 4, 8, 12, 24 };
            if (!jqs.Contains(jq))
            {
                return 0;
            }
            string sql = @"SELECT COUNT(1) FROM @tb INNER JOIN ZCYLFJWZB ON ZCYLFJWZB.GZMBH=@tb.GZMBH AND ZCYLFJWZB.YLFJBH=@tb.ZBH
	                            WHERE SYZT=1 AND @tb.CQBH=@cqbh AND ZCYLFJWZB.GZMBH=@gzmbh AND ZBH IN @bhs
                                AND ZDATETIME>=@dtStart AND ZDATETIME<=@dtEnd";

            sql = sql.Replace("@tb", "ZCSJB_JQ" + jq);
            return Db.Count(sql, new { cqbh = cqbh, gzmbh = gzmbh, bhs = bhs, dtStart = dtStart, dtEnd = dtEnd });
        }
        #endregion

        #region 历史工作阻力/阻力对比分析
        /// <summary>
        /// 按压力分站编号或位置获取历史数据
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="gzmbh">工作面编号</param>
        /// <param name="bh">压力分站编号</param>
        /// <param name="wz">压力分站位置</param>
        /// <param name="wz">压力分站位置</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        private CurveZc CurveNormal(int cqbh, int gzmbh, int bh, string wz, DateTime dtStart, DateTime dtEnd)
        {
            CurveZc rt = new CurveZc();
            IEnumerable<View_ZC_Curve> list = null;
            if (bh > 0)
            {
                list = Db.QueryWhere<View_ZC_Curve>("CQBH=@cqbh AND GZMBH=@gzmbh AND ZBH=@bh AND ZDATETIME>=@dtStart AND ZDATETIME<=@dtEnd ORDER BY ZDATETIME", new { cqbh = cqbh, gzmbh = gzmbh, bh = bh, dtStart = dtStart, dtEnd = dtEnd });
            }
            else
            {
                if (wz == "全部")
                {
                    list = Db.QueryWhere<View_ZC_Curve>("CQBH=@cqbh AND ZDATETIME>=@dtStart AND ZDATETIME<=@dtEnd ORDER BY ZDATETIME", new { cqbh = cqbh, dtStart = dtStart, dtEnd = dtEnd });
                }
                else
                {
                    list = Db.QueryWhere<View_ZC_Curve>("CQBH=@cqbh AND WZ=@wz AND ZDATETIME>=@dtStart AND ZDATETIME<=@dtEnd ORDER BY ZDATETIME", new { cqbh = cqbh, wz = wz, dtStart = dtStart, dtEnd = dtEnd });
                }
            }
            foreach (var d in list)
            {
                rt.T.Add(d.ZDATETIME);
                rt.P1.Add(d.P1);
                rt.P2.Add(d.P2);
                rt.P3.Add(d.P3);
                rt.P12.Add(d.P12);
                rt.F1.Add(d.F1);
                rt.F2.Add(d.F2);
                rt.F3.Add(d.F3);
                rt.F12.Add(d.F12);
            }
            return rt;
        }
        /// <summary>
        /// 按压力分站编号获取历史数据
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="gzmbh">工作面编号</param>
        /// <param name="bh">压力分站编号</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        public CurveZc CurveNormal(int cqbh, int gzmbh, int bh, DateTime dtStart, DateTime dtEnd)
        {
            return CurveNormal(cqbh, gzmbh, bh, null, dtStart, dtEnd);
        }
        /// <summary>
        /// 按压力分站位置获取历史数据
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="gzmbh">工作面编号</param>
        /// <param name="wz">压力分站位置</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <summary>
        public CurveZc CurveNormal(int cqbh, int gzmbh, string wz, DateTime dtStart, DateTime dtEnd)
        {
            return CurveNormal(cqbh, gzmbh, 0, wz, dtStart, dtEnd);
        }
        /// 获取加权平均数据
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="gzmbh">工作面编号</param>
        /// <param name="bh">压力分站编号</param>
        /// <param name="wz">压力分站位置</param>
        /// <param name="hSpan">加权数</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <returns>加权平均数据</returns>
        public CurveZc CurveWeighted(int cqbh, int gzmbh, int bh, string wz, int hSpan, DateTime dtStart, DateTime dtEnd)
        {
            CurveZc rt = new CurveZc();
            ZCSJB_JQ2 jq = new ZCSJB_JQ2();
            ZCSJB_JQ_WZ2 jqwz = new ZCSJB_JQ_WZ2();
            string sql = "SELECT @cols FROM @tb" + hSpan +
                " WHERE CQBH=@cqbh AND GZMBH=@gzmbh AND ZDATETIME>=@dtStart AND ZDATETIME<=@dtEnd";
            if (bh > 0)
            {
                sql = sql.Replace("@cols", string.Join(",", jq.Cols)).Replace("@tb", "ZCSJB_JQ");
                sql += " AND ZBH=@bh";
            }
            else
            {
                sql = sql.Replace("@cols", string.Join(",", jqwz.Cols)).Replace("@tb", "ZCSJB_JQ_WZ");
                if (wz != "全部")
                {
                    sql += " AND WZ=@wz";
                }
            }
            sql += " ORDER BY ZDATETIME";
            DateTime? last = null;
            foreach (var d in Db.Query<ZCSJB_JQ_WZ2>(sql, new { cqbh = cqbh, gzmbh = gzmbh, bh = bh, wz = wz, dtStart = dtStart, dtEnd = dtEnd }))
            {
                //考虑整架
                if (bh == 0 && wz == "全部")
                {
                    int count = rt.T.Count - 1;
                    if (d.ZDATETIME != last)
                    {
                        rt.T.Add(d.ZDATETIME);
                        rt.P1.Add(d.P1 / 3);
                        rt.P2.Add(d.P2 / 3);
                        rt.P3.Add(d.P3 / 3);
                        rt.P12.Add(d.ZJJQ_M / 3);
                        rt.F1.Add(d.F1 / 3);
                        rt.F2.Add(d.F2 / 3);
                        rt.F3.Add(d.F3 / 3);
                        rt.F12.Add(d.ZJJQ_K / 3);
                        last = d.ZDATETIME;
                    }
                    else
                    {
                        rt.P1[count] += d.P1 / 3;
                        rt.P2[count] += d.P2 / 3;
                        rt.P3[count] += d.P3 / 3;
                        rt.P12[count] += d.ZJJQ_M / 3;
                        rt.F1[count] += d.F1 / 3;
                        rt.F2[count] += d.F2 / 3;
                        rt.F3[count] += d.F3 / 3;
                        rt.F12[count] += d.ZJJQ_K / 3;
                    }
                }
                else
                {
                    rt.T.Add(d.ZDATETIME);
                    rt.P1.Add(d.P1);
                    rt.P2.Add(d.P2);
                    rt.P3.Add(d.P3);
                    rt.P12.Add(d.ZJJQ_M);
                    rt.F1.Add(d.F1);
                    rt.F2.Add(d.F2);
                    rt.F3.Add(d.F3);
                    rt.F12.Add(d.ZJJQ_K);
                }
            }
            return rt;
        }
        /// <summary>
        /// 计算均值，使用优化后的存储过程
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="gzmbh">工作面编号</param>
        /// <param name="bh">压力分站编号</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <param name="MPa">单位，true为Mpa，false为Kn</param>
        /// <returns>查询时间段内的均值</returns>
        public double CurveAvg(int cqbh, int gzmbh, int bh, DateTime dtStart, DateTime dtEnd, bool MPa = true)
        {
            IEnumerable<CurveZcSTAT> stats = Db.QuerySP<CurveZcSTAT>("Procedure_ZC_Curve_AVG", new { CQBH = cqbh, GZMBH = gzmbh, BH = bh, timeStart = dtStart, timeEnd = dtEnd });
            if (stats == null || stats.Count() == 0)
            {
                return 0;
            }
            CurveZcSTAT stat = stats.ElementAt(0);
            return MPa ? (stat.AVG_P1 + stat.AVG_P2) / 2 : (stat.AVG_F1 + stat.AVG_F2);
        }
        /// <summary>
        /// 计算一段时间内数据标准方差
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="gzmbh">工作面编号</param>
        /// <param name="bh">压力分站编号</param>
        /// <param name="dtStart">起始日期</param>
        /// <param name="dtEnd">结束日期</param>
        /// <param name="MPa">单位</param>
        /// <returns>查询时间段内的标准方差</returns>
        public double CurveStd(int cqbh, int gzmbh, int bh, DateTime dtStart, DateTime dtEnd, bool MPa = true)
        {
            IEnumerable<CurveZcSTAT> stats = Db.QuerySP<CurveZcSTAT>("Procedure_ZC_Curve_STD", new { CQBH = cqbh, GZMBH = gzmbh, BH = bh, timeStart = dtStart, timeEnd = dtEnd });
            if (stats == null || stats.Count() == 0)
            {
                return 0;
            }
            CurveZcSTAT stat = stats.ElementAt(0);
            return MPa ? stat.STD_P12 : stat.STD_F12;
        }
        #endregion

        #region 每日工作阻力
        /// <summary>
        /// 获取每日工作阻力(24小时加权平均值)
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="gzmbh">工作面编号</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        public List<CurveZc> CurveDate(int cqbh, int gzmbh, DateTime dtStart, DateTime dtEnd)
        {
            //获取可用传感器列表

            string sql = @"SELECT ZCYLFJWZB.YLFJBH
                            FROM SYSTEMCQB INNER JOIN
                            SYSTEMGZMB ON SYSTEMGZMB.CQBH = SYSTEMCQB.CQBH INNER JOIN
                            ZCCSB ON ZCCSB.GZMBH = SYSTEMGZMB.GZMBH INNER JOIN
                            ZCYLFJWZB ON ZCYLFJWZB.GZMBH = SYSTEMGZMB.GZMBH
                            WHERE (SYSTEMCQB.ZT = 1) AND (ZCYLFJWZB.SYZT = 1)
                            AND SYSTEMCQB.CQBH=@cqbh AND SYSTEMGZMB.GZMBH=@gzmbh
                            ORDER BY YLFJBH";
            var bhs = Db.Query<int>(sql, new { cqbh = cqbh, gzmbh = gzmbh });
            if (bhs.Count() == 0) return new List<CurveZc>();
            sql = @"SELECT ZBH,ZCYLFJWZB.DYZJBH,ZDATETIME,P1,P2,F1,F2
                          FROM SYSTEMCQB INNER JOIN SYSTEMGZMB ON SYSTEMGZMB.CQBH=SYSTEMCQB.CQBH
                          INNER JOIN ZCYLFJWZB ON ZCYLFJWZB.GZMBH=SYSTEMGZMB.GZMBH
                          INNER JOIN ZCSJB_JQ24 ON ZCSJB_JQ24.CQBH=SYSTEMCQB.CQBH AND ZCSJB_JQ24.GZMBH=ZCYLFJWZB.GZMBH AND ZCYLFJWZB.YLFJBH=ZCSJB_JQ24.ZBH
                          WHERE SYSTEMCQB.ZT=1 AND SYSTEMGZMB.ZT=1 AND ZCYLFJWZB.SYZT=1
                          AND SYSTEMCQB.CQBH=@cqbh AND SYSTEMGZMB.GZMBH=@gzmbh AND ZDATETIME>=@dtStart AND ZDATETIME<=@dtEnd
                          ORDER BY SYSTEMCQB.CQBH,SYSTEMGZMB.GZMBH,ZDATETIME,ZBH";
            IEnumerable<View_ZC_Curve> list = Db.Query<View_ZC_Curve>(sql, new { cqbh = cqbh, gzmbh = gzmbh, dtStart = dtStart, dtEnd = dtEnd });
            DateTime dt = default(DateTime);
            List<CurveZc> rt = new List<CurveZc>();
            int i = 0;
            foreach (var d in list)
            {
                if (d.ZDATETIME != dt)
                {
                    rt.Add(new CurveZc());
                    dt = d.ZDATETIME;
                    i = 0;
                }
                while (i < bhs.Count() && bhs.ElementAt(i) != d.ZBH)
                {
                    rt.Last().X.Add(d.DYZJBH);
                    rt.Last().T.Add(d.ZDATETIME);
                    rt.Last().P12.Add(0);
                    rt.Last().F12.Add(0);
                    i++;
                }
                if (i < bhs.Count() && bhs.ElementAt(i) == d.ZBH)
                {
                    rt.Last().X.Add(d.DYZJBH);
                    rt.Last().T.Add(d.ZDATETIME);
                    rt.Last().P12.Add((d.P1 + d.P2) / 2);
                    rt.Last().F12.Add((d.F1 + d.F2) / 2);
                    i++;
                }
            }
            return rt;
        }
        #endregion

        #region 频度分布分析
        /// <summary>
        /// 统计频度
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="gzmbh">工作面编号</param>
        /// <param name="zbh">压力分站编号，0为按位置统计</param>
        /// <param name="wz">压力分站位置</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <param name="channel">选择统计通道，3位二进制，由低到高分别代表P1、P2、P3 0：整架 1：P1 2：P2 4：P3</param>
        /// <returns>频度数据</returns>

        public CurveZc CurveFreq(int cqbh, int gzmbh, int zbh, string wz, DateTime dtStart, DateTime dtEnd, int channel = 0)
        {
            //统计压力值范围
            string sql = @"SELECT @col FROM View_ZC_Curve
                         WHERE CQBH=@cqbh AND GZMBH=@gzmbh AND ZDATETIME>=@dtStart AND ZDATETIME<=@dtEnd";
            object param = null;
            if (zbh == 0)
            {
                if (wz != "全部")
                {
                    sql += " AND WZ=@wz";
                }
                param = new { cqbh = cqbh, gzmbh = gzmbh, wz = wz, dtStart = dtStart, dtEnd = dtEnd };
            }
            else
            {
                sql += " AND ZBH=@zbh";
                param = new { cqbh = cqbh, gzmbh = gzmbh, zbh = zbh, dtStart = dtStart, dtEnd = dtEnd };
            }
            string colRange = @"COUNT(1) count,MAX(P1) maxP1,MAX(P2) maxP2,MAX(P3) maxP3,MAX(P1+P2)/2 maxP12,
                                MIN(P1) minP1,MIN(P2) minP2,MIN(P3) minP3,MIN(P1+P2)/2 minP12";

            CurveZcRange range = Db.Find<CurveZcRange>(sql.Replace("@col", colRange), param);
            CurveZc rst = new CurveZc();
            //根据选择的通道生成查询条件
            string query = sql.Replace("@col", "COUNT(1)");
            if (channel == 0)
            {
                query += " AND (P1+P2)/2>@pStart AND (P1+P2)/2<=@pEnd";
            }
            else
            {
                for (int i = 1; i <= 3; i++)
                {
                    if ((channel & (1 << (i - 1))) > 0)
                    {
                        query += " AND P" + i + ">=@pStart AND P" + i + "<@pEnd";
                    }
                }
            }
            int delt = 5;//频度区间步长
            for (int p = 0; p <= range.maxP1; p += delt)
            {
                rst.X.Add((p + delt).ToString());
                int c = Db.Find<int>(query.Replace("@pStart", p.ToString()).Replace("@pEnd", (p + delt).ToString()), param);
                rst.Freq.Add(100.0 * c / range.count);
            }
            return rst;
        }

        /// <summary>
        /// 按压力分站编号统计频度
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="gzmbh">工作面编号</param>
        /// <param name="zbh">压力分站编号</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <param name="channel">选择统计通道，3位二进制，由低到高分别代表P1、P2、P3 0：整架 1：P1 2：P2 4：P3</param>
        /// <returns>频度数据</returns>
        public CurveZc CurveFreq(int cqbh, int gzmbh, int zbh, DateTime dtStart, DateTime dtEnd, int channel = 0)
        {
            return CurveFreq(cqbh, gzmbh, zbh, "", dtStart, dtEnd, channel);
        }

        /// <summary>
        /// 按压力分站编号统计频度
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="gzmbh">工作面编号</param>
        /// <param name="wz">压力分站位置</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <param name="channel">选择统计通道，3位二进制，由低到高分别代表P1、P2、P3 0：整架 1：P1 2：P2 4：P3</param>
        /// <returns>频度数据</returns>
        public CurveZc CurveFreq(int cqbh, int gzmbh, string wz, DateTime dtStart, DateTime dtEnd, int channel = 0)
        {
            return CurveFreq(cqbh, gzmbh, 0, wz, dtStart, dtEnd, channel);
        }
        #endregion

        #region 综采监测报表
        /// <summary>
        /// 获取综采报表
        /// </summary>
        /// <param name="cqbh">测区编号</param>
        /// <param name="gzmbh">工作面编号</param>
        /// <param name="zbhs">压力分站编号列表</param>
        /// <param name="dtStart">起始时间</param>
        /// <param name="dtEnd">结束时间</param>
        /// <param name="channel">立柱，0：整架 1左柱 2右柱</param>
        /// <param name="minP">初撑合格值</param>
        /// <param name="isMPa">单位是否为MPa</param>
        /// <returns>返回综采报表所需所有数据</returns>
        public ReportZc Report(int cqbh, int gzmbh, int[] zbhs, DateTime dtStart, DateTime dtEnd, int channel = 0, double minP = 0, bool isMPa = true)
        {
            //对分站编号进行排序
            Array.Sort(zbhs);
            //获取工作阻力统计值
            object param = new { cqbh = cqbh, gzmbh = gzmbh, timeStart = dtStart, timeEnd = dtEnd };
            IEnumerable<AnalysisZc> gzzl = Db.QuerySP<AnalysisZc>("Procedure_ZC_Report", param);
            //计算初撑力和末阻力
            string where = @"CQBH=@cqbh AND GZMBH=@gzmbh AND ZBH IN @zbhs AND ZDATETIME>=@dtStart AND ZDATETIME<=@dtEnd
                              ORDER BY CQBH,GZMBH,ZBH,ZDATETIME";
            View_ZC_Curve last = null;
            Dictionary<int, double[]> ccl = new Dictionary<int, double[]>();//初撑力最小值、总和、总数，键为压力分站编号
            Dictionary<int, double[]> mzl = new Dictionary<int, double[]>();//末阻力最大值、总和、总数，键为压力分站编号
            int countAll = 0, countPass = 0;
            param = new { cqbh = cqbh, gzmbh = gzmbh, zbhs = zbhs, dtStart = dtStart, dtEnd = dtEnd };
            bool hasData = false;
            foreach (var d in Db.QueryWhere<View_ZC_Curve>(where, param))
            {
                hasData = true;
                if (last == null || d.ZBH != last.ZBH)
                {
                    last = d;
                    continue;
                }
                //初撑力最小、平均值
                double lastP = -1, lastF = -1;
                double thisP = -1, thisF = -1;
                switch (channel)
                {
                    case 1: lastP = last.P1; thisP = d.P1; lastF = last.F1; thisF = d.F1; break;
                    case 2: lastP = last.P2; thisP = d.P2; lastF = last.F2; thisF = d.F2; break;
                    default: lastP = last.P12; thisP = d.P12; lastF = last.F12; thisF = d.F12; break;
                }
                if ((d.ZDATETIME - last.ZDATETIME).TotalMinutes <= 10 && thisP - lastP <= (-3))
                {
                    countAll++;
                    if ((isMPa ? thisP : thisF) >= minP)
                    {
                        countPass++;
                    }
                    //this:初撑力
                    if (ccl.ContainsKey(d.ZBH))
                    {
                        if (thisP < ccl[d.ZBH][0])//最小值
                        {
                            ccl[d.ZBH][0] = isMPa ? thisP : thisF;
                        }
                        ccl[d.ZBH][1] += (isMPa ? thisP : thisF);//总和
                        ccl[d.ZBH][2] += 1;//总数
                    }
                    else
                    {
                        ccl.Add(d.ZBH, new double[] { isMPa ? thisP : thisF, isMPa ? thisP : thisF, 1 });
                    }
                    //last:末阻力
                    if (mzl.ContainsKey(d.ZBH))
                    {
                        if (lastP > mzl[d.ZBH][0])//最大值
                        {
                            mzl[d.ZBH][0] = isMPa ? lastP : lastF;
                        }
                        mzl[d.ZBH][1] += isMPa ? lastP : lastF;//总和
                        mzl[d.ZBH][2] += 1;//总数
                    }
                    else
                    {
                        mzl.Add(d.ZBH, new double[] { isMPa ? lastP : lastF, isMPa ? lastP : lastF, 1 });
                    }
                }
                last = d;
            }
            ReportZc r = new ReportZc();
            if (countAll > 0)
            {
                r.PoP = 1.0 * countPass / countAll;
            }
            if (!hasData)
            {
                r.WHOLE[0] = 0; r.WHOLE[1] = 0; r.WHOLE[2] = 0;
                r.TOP[0] = 0; r.TOP[1] = 0; r.TOP[2] = 0;
                r.MID[0] = 0; r.MID[1] = 0; r.MID[2] = 0;
                r.BOTTOM[0] = 0; r.BOTTOM[1] = 0; r.BOTTOM[2] = 0;
                return r;
            }
            //重整形式，以便进行报表输出。计算整面、上、中、下部工作阻力最大、最小、平均值
            int[] count = new int[] { 0, 0, 0 };//上部、中部、下部数量
            double[] sum = new double[] { 0, 0, 0 };
            countAll = 0;
            foreach (var zl in gzzl)
            {
                if (!zbhs.Contains(zl.ZBH))
                {
                    continue;
                }
                countAll++;
                r.YLFZBH.Add(zl.ZBH);
                r.DYZJBH.Add(zl.DYZJBH);
                r.WZ.Add(zl.WZ);
                double[] statWZ = null;//统计位置
                int index = 0;//统计总值
                switch (zl.WZ)
                {
                    case "上部": statWZ = r.TOP; index = 0; break;
                    case "中部": statWZ = r.MID; index = 1; break;
                    case "下部": statWZ = r.BOTTOM; index = 2; break;
                }
                count[index]++;
                switch (channel)
                {
                    case 1:
                        if (isMPa)
                        {
                            r.MaxZL.Add(zl.MaxP1);
                            r.AvgZL.Add(zl.AvgP1);
                            if (zl.MaxP1 > r.WHOLE[0]) r.WHOLE[0] = zl.MaxP1;
                            if (zl.MinP1 < r.WHOLE[1]) r.WHOLE[1] = zl.MinP1;
                            if (zl.MaxP1 > statWZ[0]) statWZ[0] = zl.MaxP1;
                            if (zl.MinP1 < statWZ[1]) statWZ[1] = zl.MinP1;
                            r.WHOLE[2] += zl.AvgP1;
                            sum[index] += zl.AvgP1;
                        }
                        else
                        {
                            r.MaxZL.Add(zl.MaxF1);
                            r.AvgZL.Add(zl.AvgF1);
                            if (zl.MaxF1 > r.WHOLE[0]) r.WHOLE[0] = zl.MaxF1;
                            if (zl.MinF1 < r.WHOLE[1]) r.WHOLE[1] = zl.MinF1;
                            if (zl.MaxF1 > statWZ[0]) statWZ[0] = zl.MaxF1;
                            if (zl.MinF1 < statWZ[1]) statWZ[1] = zl.MinF1;
                            r.WHOLE[2] += zl.AvgF1;
                            sum[index] += zl.AvgF1;
                        }
                        break;
                    case 2:
                        if (isMPa)
                        {
                            r.MaxZL.Add(zl.MaxP2);
                            r.AvgZL.Add(zl.AvgP2);
                            if (zl.MaxP2 > r.WHOLE[0]) r.WHOLE[0] = zl.MaxP2;
                            if (zl.MinP2 < r.WHOLE[1]) r.WHOLE[1] = zl.MinP2;
                            if (zl.MaxP2 > statWZ[0]) statWZ[0] = zl.MaxP2;
                            if (zl.MinP2 < statWZ[1]) statWZ[1] = zl.MinP2;
                            r.WHOLE[2] += zl.AvgP2;
                            sum[index] += zl.AvgP2;
                        }
                        else
                        {
                            r.MaxZL.Add(zl.MaxF2);
                            r.AvgZL.Add(zl.AvgF2);
                            if (zl.MaxF2 > r.WHOLE[0]) r.WHOLE[0] = zl.MaxF2;
                            if (zl.MinF2 < r.WHOLE[1]) r.WHOLE[1] = zl.MinF2;
                            if (zl.MaxF2 > statWZ[0]) statWZ[0] = zl.MaxF2;
                            if (zl.MinF2 < statWZ[1]) statWZ[1] = zl.MinF2;
                            r.WHOLE[2] += zl.AvgF2;
                            sum[index] += zl.AvgF2;
                        }
                        break;
                    default:
                        if (isMPa)
                        {
                            r.MaxZL.Add(zl.MaxP12);
                            r.AvgZL.Add(zl.AvgP12);
                            if (zl.MaxP12 > r.WHOLE[0]) r.WHOLE[0] = zl.MaxP12;
                            if (zl.MinP12 < r.WHOLE[1]) r.WHOLE[1] = zl.MinP12;
                            if (zl.MaxP12 > statWZ[0]) statWZ[0] = zl.MaxP12;
                            if (zl.MinP12 < statWZ[1]) statWZ[1] = zl.MinP12;
                            r.WHOLE[2] += zl.AvgP12;
                            sum[index] += zl.AvgP12;
                        }
                        else
                        {
                            r.MaxZL.Add(zl.MaxF12);
                            r.AvgZL.Add(zl.AvgF12);
                            if (zl.MaxF12 > r.WHOLE[0]) r.WHOLE[0] = zl.MaxF12;
                            if (zl.MinF12 < r.WHOLE[1]) r.WHOLE[1] = zl.MinF12;
                            if (zl.MaxF12 > statWZ[0]) statWZ[0] = zl.MaxF12;
                            if (zl.MinF12 < statWZ[1]) statWZ[1] = zl.MinF12;
                            r.WHOLE[2] += zl.AvgF12;
                            sum[index] += zl.AvgF12;
                        }
                        break;
                }
            }
            if (countAll > 0) r.WHOLE[2] = r.WHOLE[2] / countAll;
            if (count[0] > 0) r.TOP[2] = sum[0] / count[0];
            if (count[1] > 0) r.MID[2] = sum[1] / count[1];
            if (count[2] > 0) r.BOTTOM[2] = sum[2] / count[2];
            foreach (int zbh in zbhs)
            {
                if (ccl.ContainsKey(zbh))
                {
                    r.MinCCL.Add(ccl[zbh][0]);
                    if (ccl[zbh][2] > 0) r.AvgCCL.Add(ccl[zbh][1] / ccl[zbh][2]);
                }
                else
                {
                    r.MinCCL.Add(-1);
                    r.AvgCCL.Add(-1);
                }
                if (mzl.ContainsKey(zbh))
                {
                    r.MaxMZL.Add(mzl[zbh][0]);
                    if (mzl[zbh][2] > 0) r.AvgMZL.Add(mzl[zbh][1] / mzl[zbh][2]);
                }
                else
                {
                    r.MaxMZL.Add(-1);
                    r.AvgMZL.Add(-1);
                }
            }
            return r;
        }
        #endregion

        public static void Test()
        {
            //CurveZc rt = new DataZcService().CurveNormal(1, 1, new DateTime(2017, 12, 1), new DateTime(2017, 12, 2));
            //CurveZc rt = new DataZcService().CurveWeighted(1, 1, "下部", 2, new DateTime(2019, 1, 4), new DateTime(2019, 1, 5));
            //CurveZc rt = new DataZcService().GetActive(1, 1);
            //ZCCSB csb = new DataZcService().GetIni(6);
            //double avg = new DataZcService().CurveAvg(1, 1, 1, new DateTime(2019, 1, 4), new DateTime(2019, 1, 5),false);
            //double std = new DataZcService().CurveStd(1, 1, 1, new DateTime(2019, 1, 4), new DateTime(2019, 1, 5), false);
            //IEnumerable<View_ZC_Active>  list= new DataZcService().Query(1, 1, true,new int[]{1,2}, new DateTime(2018, 10, 1), new DateTime(2018, 10, 2),24,"",-1,0);
            //CurveZc rt = new DataZcService().CurveFreq(1, 1, 1, new DateTime(2018, 1, 19), new DateTime(2018, 1, 21), 1);
            //CurveZc rt = new DataZcService().CurveFreq(1, 1, "上部", new DateTime(2018, 9, 26), new DateTime(2018, 9, 27), 0);
            //IEnumerable<AnalysisZc> list = new DataZcService().Analysis(1, 1,new int[]{1,2}, new DateTime(2018, 11, 1), new DateTime(2018, 11, 4),24, 0);
            //ReportZc list = new DataZcService().Report(1, 1, new int[]{/*1,2,3,4,*/5},new DateTime(2018, 1, 19), new DateTime(2018, 1, 20),0,30.2,true);
            List<CurveZc> rt = new DataZcService().CurveDate(1, 1, new DateTime(2018, 1, 19), new DateTime(2018, 1, 20));
            Console.WriteLine();
        }
    }


    #region 保存结果的类
    /// <summary>
    /// 综采查询返回结果，用于绘图
    /// </summary>
    public class CurveZc
    {
        /// <summary>
        /// 是否报警
        /// </summary>
        public bool WarnNow = false;
        /// <summary>
        /// 时间
        /// </summary>
        public List<DateTime> T = new List<DateTime>();
        /// <summary>
        /// 横轴标记
        /// </summary>
        public List<string> X = new List<string>();
        /// <summary>
        /// 工作状态
        /// </summary>
        public List<string> S = new List<string>();
        /// <summary>
        /// 安装位置
        /// </summary>
        public List<string> WZ = new List<string>();
        /// <summary>
        /// 频度，百分比
        /// </summary>
        public List<double> Freq = new List<double>();
        #region 压力数据序列
        public List<double> P1 = new List<double>();
        public List<double> P2 = new List<double>();
        public List<double> P3 = new List<double>();
        /// <summary>
        /// 整架值-MPa
        /// </summary>
        public List<double> P12 = new List<double>();
        public List<double> F1 = new List<double>();
        public List<double> F2 = new List<double>();
        public List<double> F3 = new List<double>();
        /// <summary>
        /// 整架值-Kn
        /// </summary>
        public List<double> F12 = new List<double>();
        #endregion
    }
    /// <summary>
    /// 综采统计值返回结果
    /// </summary>
    class CurveZcSTAT
    {
        public int ZBH;
        public double AVG_P1;
        public double AVG_P2;
        public double AVG_P3;
        public double AVG_P12;
        public double AVG_F1;
        public double AVG_F2;
        public double AVG_F3;
        public double AVG_F12;
        public double STD_P1;
        public double STD_P2;
        public double STD_P3;
        public double STD_P12;
        public double STD_F1;
        public double STD_F2;
        public double STD_F3;
        public double STD_F12;
    }
    /// <summary>
    /// 压力范围统计结果
    /// </summary>
    class CurveZcRange
    {
        public int count;
        public double maxP1;
        public double maxP2;
        public double maxP3;
        public double maxP12;
        public double minP1;
        public double minP2;
        public double minP3;
        public double minP12;
    }
    /// <summary>
    /// 综采分析列表的行
    /// </summary>
    class AnalysisZc
    {
        public Int32 CQBH { get; set; }
        public Int32 GZMBH { get; set; }
        public Int32 ZBH { get; set; }
        public String DYZJBH { get; set; }
        public String WZ { get; set; }
        public DateTime ZDATETIME { get; set; }
        public Double MaxP1 { get; set; }
        public Double MinP1 { get; set; }
        public Double AvgP1 { get; set; }
        public Double MaxP2 { get; set; }
        public Double MinP2 { get; set; }
        public Double AvgP2 { get; set; }
        public Double MaxP3 { get; set; }
        public Double MinP3 { get; set; }
        public Double AvgP3 { get; set; }
        public Double MaxP12 { get; set; }
        public Double MinP12 { get; set; }
        public Double AvgP12 { get; set; }
        public Double MaxF1 { get; set; }
        public Double MinF1 { get; set; }
        public Double AvgF1 { get; set; }
        public Double MaxF2 { get; set; }
        public Double MinF2 { get; set; }
        public Double AvgF2 { get; set; }
        public Double MaxF3 { get; set; }
        public Double MinF3 { get; set; }
        public Double AvgF3 { get; set; }
        public Double MaxF12 { get; set; }
        public Double MinF12 { get; set; }
        public Double AvgF12 { get; set; }
    }
    /// <summary>
    /// 综采报表
    /// </summary>
    class ReportZc
    {
        /// <summary>
        /// 整部最大、最小、平均值
        /// </summary>
        public double[] WHOLE = new double[] { double.MinValue, double.MaxValue, 0 };
        /// <summary>
        /// 上部最大、最小、平均值
        /// </summary>
        public double[] TOP = new double[] { double.MinValue, double.MaxValue, 0 };
        /// <summary>
        /// 中部最大、最小、平均值
        /// </summary>
        public double[] MID = new double[] { double.MinValue, double.MaxValue, 0 };
        /// <summary>
        /// 下部最大、最小、平均值
        /// </summary>
        public double[] BOTTOM = new double[] { double.MinValue, double.MaxValue, 0 };
        /// <summary>
        /// 压力分站编号
        /// </summary>
        public List<int> YLFZBH = new List<int>();
        /// <summary>
        /// 对应支架编号
        /// </summary>
        public List<string> DYZJBH = new List<string>();
        /// <summary>
        /// 安装位置
        /// </summary>
        public List<string> WZ = new List<string>();
        //最大工作阻力
        public List<double> MaxZL = new List<double>();
        /// <summary>
        /// 平均工作阻力
        /// </summary>
        public List<double> AvgZL = new List<double>();
        /// <summary>
        /// 最小初撑力
        /// </summary>
        public List<double> MinCCL = new List<double>();
        /// <summary>
        /// 平均初撑力
        /// </summary>
        public List<double> AvgCCL = new List<double>();
        /// <summary>
        /// 最大末阻力
        /// </summary>
        public List<double> MaxMZL = new List<double>();
        /// <summary>
        /// 平均末阻力
        /// </summary>
        public List<double> AvgMZL = new List<double>();
        /// <summary>
        /// 合格率
        /// </summary>
        public double PoP = 0;
    }
    #endregion
}
